/**Aaron Flaaen*/
/**Updated August 23, 2017 */
/**This File Creates the quarterly firm-level employment file */

libname fromssl  "";
libname outloc "";

/** ****************************************************** */
/** 2009-2012						  */
/** ***************************************************  */

%macro byyear(year);
/* Load in the SU Data, and split into SU and SBM pieces */
data sslyrsu;
	set fromssl.ssl&year.su (keep = rqp1 empunit_typ rqp1f acqp1 apqp1 apqp1_wage acqp1_wage acqp2 acqp3 acqp4 rap rapf EIN EMP_IRS_CURR_&year.Q2 EMP_IRS_CURR_&year.Q3 EMP_IRS_CURR_&year.Q4 recnum cfn);
 	mu = 0;
run;

data sslyrsu_sbm;
	set sslyrsu;
	if empunit_typ="SBM";
run;

data sslyrsu_nonsbm;
	set sslyrsu;
	if empunit_typ="SU";
	ein2 = strip(ein);
run;


/* Load in the MU Data */
data sslyrmu;
	set fromssl.ssl&year.mu (keep = rqp1 rqp1f alpha rap rapf EIN recnum );
 	mu = 1;
	rqp1fmu = rqp1f;
	rqp1mu = rqp1;
	rapmu = rap;
	rapfmu = rapf;
	
run;

data sslyrmu;
	set sslyrmu (drop = rapf rap rqp1 rqp1f);
run;



/* Deal with duplicate EINS on both  and SBM side */

/* Duplicate EINS on SBM Side*/
proc freq data=sslyrsu_sbm noprint;
	tables ein/out=freqout;
run;

proc freq data=freqout;
	tables count;
run;

proc sort data=sslyrsu_sbm;
	by ein;
run;

proc sort data=freqout (drop=percent);
	by ein;
run;

data sslyrsu_sbm2;
	merge sslyrsu_sbm (in=data1) freqout (in=data2);
	by ein;
	if data1 and not data2 then m_var=1;
	if not data1 and data2 then m_var=2;
	if data1 and data2 then m_var=3;
run;

data sslyrsu_sbm2;
	set sslyrsu_sbm2 (drop=m_var);
	if count>1 AND rqp1=. then delete;
run;

/*Try again */
proc freq data=sslyrsu_sbm2 noprint;
	tables ein/out=freqout2;
run;

proc freq data=freqout2 (drop=percent);
	tables count;
run;

proc sort data=sslyrsu_sbm2 (drop=count);
	by ein;
run;

proc sort data=freqout2;
	by ein;
run;

data sslyrsu_sbm3;
	merge sslyrsu_sbm2 (in=data1) freqout2 (in=data2);
	by ein;
	if data1 and not data2 then m_var=1;
	if not data1 and data2 then m_var=2;
	if data1 and data2 then m_var=3;
run;

data sslyrsu_sbm3;
	set sslyrsu_sbm3 (drop=m_var);
	if count>1 then delete;
run;


/*Now merge together the MU and SBM Data */
proc sort data=sslyrmu;
	by ein;
run;

data bothssl;
	merge sslyrsu_sbm3 (in=data1) sslyrmu (in=data2);
	by ein;
	if data1 and not data2 then m_var=1;
	if not data1 and data2 then m_var=2;
	if data1 and data2 then m_var=3;
run;

proc freq data=bothssl;
	tables m_var;
run;

/*Collapse by Alpha */
proc sort data=bothssl nodupkey;
	by ein alpha;
run;

data bothssl;
	set bothssl (keep=ein alpha rqp1 rap acqp1 acqp2 acqp3 acqp4 EMP_IRS_CURR_&year.Q2 EMP_IRS_CURR_&year.Q3 EMP_IRS_CURR_&year.Q4);
run;

proc sort data=bothssl;
by alpha;
run;

proc means data = bothssl noprint;
	by alpha;
	vars rqp1 rap acqp1 acqp2 acqp3 acqp4 EMP_IRS_CURR_&year.Q2 EMP_IRS_CURR_&year.Q3 EMP_IRS_CURR_&year.Q4;
	output out=alphabothssl sum(rqp1 rap acqp1 acqp2 acqp3 acqp4 EMP_IRS_CURR_&year.Q2 EMP_IRS_CURR_&year.Q3 EMP_IRS_CURR_&year.Q4) = rqp1 rap acqp1 acqp2 acqp3 acqp4 EMP_IRS_CURR_&year.Q2 EMP_IRS_CURR_&year.Q3 EMP_IRS_CURR_&year.Q4;


/*Export to Stata */
proc export data = alphabothssl
	outfile = "ssl_mu_quart_emp_&year"
	dbms = stata replace;
run;

/*Export to Stata */
proc export data = sslyrsu_nonsbm
	outfile = "ssl_su_quart_emp_&year"
	dbms = stata replace;
run;



%mend;
%byyear(2009); 

%byyear(2010); 

 %byyear(2011); 

%byyear(2012);
